package nl.saxion.act.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import nl.saxion.act.db.DatabaseManager;
import nl.saxion.act.wt.Bid;
import nl.saxion.act.wt.Person;


public class PersonDAO {

	private String sqlGetPersonByID   	= "SELECT * FROM Person WHERE personID = ? " ;
	private String sqlGetAllPerson   	= "SELECT * FROM Person" ;
	private String sqlGetPersonByName  	= "SELECT * FROM Person WHERE username = ? " ;
	private String sqlInsertPerson  	= "INSERT INTO Person (name, adress, city, email, username, password) VALUES (?, ?, ?, ?, ?, ?)" ;
	private String count				= "SELECT Count(*) FROM Person";
	private String sqlGetPersonNameById = "SELECT name FROM Person WHERE PersonID = ?";
	private String sqlGetLastFewBidders = "SELECT name FROM	Person ";
	
	private Connection        con      		= null ;
	private PreparedStatement psAllPerson   = null ;
	private PreparedStatement psPerson   	= null ;
	private PreparedStatement psPersonName 	= null;
	private PreparedStatement psInsertPerson= null;
	private PreparedStatement psCount 		= null;
	private PreparedStatement psGetName		= null;

	private int countPersons          = 0;
	private boolean loggedOn = false;
	
	public PersonDAO(DatabaseManager myDb){

		con = myDb.getConnection();
		try{
			
			this.psPerson   	= con.prepareStatement(sqlGetPersonByID) ;
			this.psPersonName 	= con.prepareStatement(sqlGetPersonByName);
			this.psAllPerson	= con.prepareStatement(sqlGetAllPerson);
			this.psInsertPerson = con.prepareStatement(sqlInsertPerson);
			this.psCount		= con.prepareStatement(count);
			this.psGetName		= con.prepareStatement(sqlGetPersonNameById);
			
		    Statement stmt     = con.createStatement() ;
			ResultSet rs       = stmt.executeQuery("SELECT count(*) FROM Person") ;
			
			if (rs.next()) {
				countPersons = rs.getInt(1) ;
			}
			
			rs.close() ;
			stmt.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}
	}
	
	public Person getPerson(int findPersonID){
		Person person = new Person();

		try{
			psPerson.clearParameters();
			
			psPerson.setInt(1, findPersonID);
			
			ResultSet rs = psPerson.executeQuery();
			
			while (rs.next()) {
				int personID      		= rs.getInt		("personID");
				String name      		= rs.getString  ("name");
				String adress      		= rs.getString  ("adress");
				String city      		= rs.getString  ("city");
				String email			= rs.getString	("email");
				String username      	= rs.getString  ("username");
				String password      	= rs.getString  ("password");
			
				Person person1 	= new Person(personID, name, adress, city, email, username, password);
				person = person1;
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return person;
	}	
	
	public ArrayList<Person> getPersonByName(String findPersonName){
		ArrayList<Person> persons = new ArrayList<Person>();

		try{
			psPersonName.clearParameters();
			
			psPersonName.setString(1, findPersonName);
			
			ResultSet rs = psPersonName.executeQuery();
			
			while (rs.next()) {
				int personID      		= rs.getInt		("personID");
				String name      		= rs.getString  ("name");
				String adress      		= rs.getString  ("adress");
				String city      		= rs.getString  ("city");
				String email			= rs.getString	("email");
				String username      	= rs.getString  ("username");
				String password      	= rs.getString  ("password");
			
				Person person 	= new Person(personID, name, adress, city, email, username, password);
				persons.add(person);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}

		return persons;
	}	
		
	public ArrayList<Person> getAllPerson(){
		ArrayList<Person> persons = new ArrayList<Person>();

		try{
		
			
			ResultSet rs = psAllPerson.executeQuery();
			
			while (rs.next()) {
				int personID      		= rs.getInt		("personID");
				String name      		= rs.getString  ("name");
				String adress      		= rs.getString  ("adress");
				String city      		= rs.getString  ("city");
				String email			= rs.getString	("email");
				String username      	= rs.getString  ("username");
				String password     	= rs.getString  ("password");
			
				Person person 	= new Person(personID, name, adress, city, email, username, password);
				persons.add(person);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			
			printSQLException(se) ;
			se.printStackTrace();
		}

		return persons;
	}	
	
	public Person insertPerson(String name, String adress, String city, String email, String username, String password) {
		try {			
			psInsertPerson.clearParameters();

			psInsertPerson.setString(1, name);
			psInsertPerson.setString(2, adress);
			psInsertPerson.setString(3, city);
			psInsertPerson.setString(4, email);
			psInsertPerson.setString(5, username);
			psInsertPerson.setString(6, password);
			
			psInsertPerson.executeUpdate();
			
			ResultSet rs = psCount.executeQuery();
			while(rs.next()){
				countPersons = rs.getInt(1);
			}
			return getPerson(countPersons);
			
		} catch (SQLException se) {
			printSQLException(se);
			return null;
		}

	}
	
	public Person login(String userName, String password){
		for(Person p : getPersonByName(userName)){
			if(p.getPassword().equalsIgnoreCase(password)){
				loggedOn = true;
				return p;
			}
			else
				loggedOn  = false;
		}
		return null;
	}
		
	public int getCountPersons() {
		return countPersons;
	}
	
	public boolean getLoggedOn(){
		return loggedOn;
	}

	private void printSQLException(SQLException se) {
		while(se != null) {

			System.out.print("SQLException: State:   " + se.getSQLState());
			System.out.println("Severity: " + se.getErrorCode());
			System.out.println(se.getMessage());			

			se = se.getNextException();
		}
	}

	public String getBidderNameByID(int bidderID) {
		String bidderName = "";
		try {
			psGetName.clearParameters();
			psGetName.setInt(1, bidderID);
		
			ResultSet rs = psGetName.executeQuery();
			while(rs.next()){
				bidderName = rs.getString(1);
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return bidderName;
	}

}
